package smartcrud.spring.models.rbac;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import smartcrud.common.orm.Page;
import smartcrud.common.orm.PropertyFilter;
import smartcrud.common.orm.jdbc.Sql;

@Repository
public class UserDaoJdbcImpl implements UserDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public User createUser(final User o) {
		final String sql = "insert into RBAC_USER(EMAIL,USERNAME,PASSWORD,FULLNAME,SEX,BIRTH,IS_ADMIN,STATUS) values (?,?,?,?,?,now(),?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(/* sql, */new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				int z = 0;
				ps.setString(++z, o.getEmail());
				ps.setString(++z, o.getUsername());
				ps.setString(++z, o.getPassword());
				ps.setString(++z, o.getFullname());
				ps.setString(++z, o.getSex());
				ps.setBoolean(++z, o.getIsAdmin());
				ps.setByte(++z, o.getStatus());
				return ps;
			}
		}, keyHolder);
		o.setId(keyHolder.getKey().longValue());
		return o;
	}

	@Override
	public void assignUserWithRoles(User user) {

		List batchArgs = new ArrayList();
		if (user.getRoles()!=null) {
			for (Role role : user.getRoles()) {
				Object[] args = new Object[] { user.getId(), role.getId() };
				batchArgs.add(args);
			}
		}
		//remove all
		jdbcTemplate.update("delete from rbac_user_role where user_id=?", user.getId());

		//assign again
		String sql = "insert into rbac_user_role(user_id,role_id) values(?,?)";
		jdbcTemplate.batchUpdate(sql, batchArgs);
	}

	@Override
	public void deleteUser(Long id) {
		jdbcTemplate.update("delete from RBAC_USER_ROLE where USER_ID=?", id);
		jdbcTemplate.update("delete from RBAC_USER where ID=?", id);
	}

	@Override
	public void updateUser(User o) {
		final String sql = "update RBAC_USER set EMAIL=?,USERNAME=?,PASSWORD=?,FULLNAME=?,SEX=?,BIRTH=?,IS_ADMIN=?,STATUS=? where ID=?";
		jdbcTemplate.update(sql, o.getEmail(), o.getUsername(),
				o.getPassword(), o.getFullname(), o.getSex(), o.getBirth(),
				o.getIsAdmin(), o.getStatus(), o.getId());
	}

	@Override
	public User findUserById(Long id) {
		final String sql = "select ID,EMAIL,USERNAME,PASSWORD,FULLNAME,SEX,BIRTH,IS_ADMIN,STATUS from RBAC_USER where ID=?";
		User user = jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
			@Override
			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User o = new User();
				o.setId(rs.getLong("ID"));
				o.setEmail(rs.getString("EMAIL"));
				o.setUsername(rs.getString("USERNAME"));
				o.setPassword(rs.getString("PASSWORD"));
				o.setFullname(rs.getString("FULLNAME"));
				o.setSex(rs.getString("SEX"));
				o.setBirth(rs.getDate("BIRTH"));
				o.setIsAdmin(rs.getBoolean("IS_ADMIN"));
				o.setStatus(rs.getByte("STATUS"));
				return o;
			}
		}, id);
		user.setRoles(new HashSet(findUserRoles(id)));

		return user;
	}

	private List<Role> findUserRoles(Long userId) {
		String sql = "select r.* from RBAC_ROLE r, RBAC_USER_ROLE ur "
				+ "where r.ID = ur.ROLE_ID  and ur.USER_ID=?";

		List<Role> content = jdbcTemplate.query(sql, new RowMapper<Role>() {
			@Override
			public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
				Role r = new Role();
				r.setId(rs.getLong("id"));
				r.setName(rs.getString("name"));
				return r;
			}
		}, userId);

		return content;
	}

	@Override
	public List<User> findAllUsers() {
		final String sql = "select ID,EMAIL,USERNAME,PASSWORD,FULLNAME,SEX,BIRTH,IS_ADMIN,STATUS from RBAC_USER";
		return jdbcTemplate.query(sql, new RowMapper<User>() {
			@Override
			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User o = new User();
				o.setId(rs.getLong("ID"));
				o.setEmail(rs.getString("EMAIL"));
				o.setUsername(rs.getString("USERNAME"));
				o.setPassword(rs.getString("PASSWORD"));
				o.setFullname(rs.getString("FULLNAME"));
				o.setSex(rs.getString("SEX"));
				o.setBirth(rs.getDate("BIRTH"));
				o.setIsAdmin(rs.getBoolean("IS_ADMIN"));
				o.setStatus(rs.getByte("STATUS"));
				return o;
			}
		});
	}

	@Override
	public int count(Page<User> page, final List<PropertyFilter> filters) {
		Sql sql = new Sql("select count(*) from RBAC_USER where 1=1 ", page,
				filters);
		sql.build4count();
		return jdbcTemplate.queryForInt(sql.getSqlBody(), sql.getSqlParams());
	}

	@Override
	public Page<User> findByPage(Page<User> page,
			final List<PropertyFilter> filters) {
		if (page.isAutoCount()) {
			int totalCount = count(page, filters);
			page.setTotalCount(totalCount);
		}

		Sql sql = new Sql("select * from RBAC_USER where 1=1 ", page, filters);
		sql.build();

		List<User> content = jdbcTemplate.query(sql.getSqlBody(),
				new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						User o = new User();
						o.setId(rs.getLong("ID"));
						o.setEmail(rs.getString("EMAIL"));
						o.setUsername(rs.getString("USERNAME"));
						o.setPassword(rs.getString("PASSWORD"));
						o.setFullname(rs.getString("FULLNAME"));
						o.setSex(rs.getString("SEX"));
						o.setBirth(rs.getDate("BIRTH"));
						o.setIsAdmin(rs.getBoolean("IS_ADMIN"));
						o.setStatus(rs.getByte("STATUS"));
						return o;
					}
				}, sql.getSqlParams());

		page.setContent(content);
		return page;
	}

	@Override
	public User connect(String email, String password) {
		final String sql = "select ID,EMAIL,USERNAME,PASSWORD,FULLNAME,SEX,BIRTH,IS_ADMIN,STATUS from RBAC_USER where EMAIL=? and PASSWORD=?";
		try {
			return jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
				@Override
				public User mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					User o = new User();
					o.setId(rs.getLong("ID"));
					o.setEmail(rs.getString("EMAIL"));
					o.setUsername(rs.getString("USERNAME"));
					// o.setPassword(rs.getString("PASSWORD"));
					o.setFullname(rs.getString("FULLNAME"));
					o.setSex(rs.getString("SEX"));
					o.setBirth(rs.getDate("BIRTH"));
					o.setIsAdmin(rs.getBoolean("IS_ADMIN"));
					o.setStatus(rs.getByte("STATUS"));
					return o;
				}
			}, email, password);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	public List<Menu> getUserMenus(Long userId) {
		final String sql = "select m.* from rbac_menu m "
				+ "join rbac_role_menu rm on m.id=rm.menu_id "
				+ "join rbac_role r on rm.role_id = r.id "
				+ "join rbac_user_role ur on ur.role_id = r.id "
				+ "join rbac_user u on ur.user_id = u.id and u.id=? order by m.menu_order asc";
		return jdbcTemplate.query(sql, new RowMapper<Menu>() {
			@Override
			public Menu mapRow(ResultSet rs, int rowNum) throws SQLException {
				Menu o = new Menu();
				o.setId(rs.getLong("ID"));
				o.setComment(rs.getString("COMMENT"));
				o.setLevel(rs.getInt("LEVEL"));
				o.setName(rs.getString("NAME"));
				o.setMenuOrder(rs.getInt("MENU_ORDER"));
				o.setUrl(rs.getString("URL"));
				o.setPid(rs.getLong("PID"));
				return o;
			}
		}, userId);
	}
}
